package com.abel.bigwater.wflow.mapper

import com.abel.bigmeter.service.LoginRequest
import com.abel.bigwater.model.BwRight
import com.abel.bigwater.model.BwRole
import com.abel.bigwater.model.BwUser
import com.abel.bigwater.model.BwUserLogin
import org.apache.ibatis.annotations.*
import java.util.*

@Mapper
interface UserMapper {
    /**
     * This method is called by user-manager use-case.
     */
    @Select("""<script>
        SELECT u.user_id AS `id`, user_name AS `name`, user_mobile AS mobile, user_email AS email
            , pass_hash AS passHash, user_status AS status
            , u.firm_id AS firmId
            , f.firm_name AS firmName
            , email_valid AS emailValid, email_token AS emailToken, user_token AS userToken
            , u.create_by AS createBy, u.create_date AS createDate
            , u.update_by AS updateBy, u.update_date AS updateDate
            , MAX(ul.login_time) AS lastLoginTime
        FROM bw.bw_user u LEFT JOIN bw.bw_user_login ul ON u.user_id = ul.user_id
        LEFT JOIN bw.bw_firm f ON u.firm_id = f.firm_id
        WHERE u.firm_id LIKE #{firmId}
        <when test='idStr != null'>
            AND u.user_id LIKE #{idStr}
        </when>
        <when test='nameStr != null'>
            AND u.user_name LIKE #{nameStr}
        </when>
        GROUP BY u.user_id
        </script>""")
    fun listUser(@Param("firmId") firmId: String, @Param("idStr") idStr: String?, @Param("nameStr") nameStr: String?): List<BwUser>

    @Select("""<script>
        SELECT DISTINCT
         u.user_id AS `id`, u.user_name AS `name`, u.user_mobile AS mobile, u.user_email AS email
         , u.pass_hash AS passHash, u.user_status AS status
         , u.firm_id AS firmId
         , f.firm_name AS firmName
         , u.email_valid AS emailValid, u.email_token AS emailToken, u.user_token AS userToken
        , u.create_by AS createBy, u.create_date AS createDate
        , u.update_by AS updateBy, u.update_date AS updateDate
        , MAX(ul.login_time) AS lastLoginTime
        FROM bw_user u LEFT JOIN bw_user_login ul ON u.user_id = ul.user_id
        JOIN bw_user_role ur ON u.user_id = ur.user_id
        LEFT JOIN bw_firm f ON u.firm_id = f.firm_id
        WHERE ur.role_name = #{roleName}
        GROUP BY u.user_id
        </script>""")
    fun listUserByRole(@Param("roleName") roleName: String?): List<BwUser>

    /**
     * This method is called by user-login use-case.
     */
    @Select("""
        SELECT u.user_id as `id`, user_name as `name`, user_mobile as mobile,
        user_email as email, user_status as status, pass_hash as passHash,
        u.firm_id AS firmId,
        f.firm_name AS firmName,
        email_valid AS emailValid,
        email_token AS emailToken,
        user_token AS userToken
        , u.create_by AS createBy, u.create_date AS createDate
        , u.update_by AS updateBy, u.update_date AS updateDate
        , MAX(ul.login_time) AS lastLoginTime
        FROM bw_user u LEFT JOIN bw_user_login ul ON u.user_id = ul.user_id
        LEFT JOIN bw_firm f ON u.firm_id = f.firm_id
        WHERE u.user_id = #{userId}
        OR user_mobile = #{userId}
        OR user_email = #{userId}
        GROUP BY u.user_id
""")
    fun selectUser(@Param("userId") userId: String): BwUser

    /**
     * insertUser commits to the database.
     */
    @Insert("""
        INSERT INTO bw_user(user_id, user_name, user_mobile, user_email
        , pass_hash, user_status, firm_id
        , email_valid, email_token, user_token
        , create_by)
        VALUES(#{id}, #{name}, #{mobile}, #{email}
        , #{passHash}, #{status}, #{firmId}
        , #{emailValid}, #{emailToken}, #{userToken}
        , #{createBy})
        """)
    fun insertUser(user: BwUser): Int

    @Delete("""
        DELETE FROM bw_user
        WHERE user_id = #{userId}
        """)
    fun deleteUser(userId: String): Int

    @Update("""<script>
        UPDATE bw_user
        <set>
            <when test='status != null'>
	            user_status = #{status},
            </when>
            <when test='firmId != null'>
	            firm_id = #{firmId},
            </when>
	        <when test='name != null'>
                user_name = #{name},
	        </when>
	        <when test='mobile != null'>
                user_mobile = #{mobile},
	        </when>
	        <when test='email != null'>
                user_email = #{email},
	        </when>
	        <when test='passHash != null'>
	            pass_hash = #{passHash},
	        </when>
            update_by = #{updateBy},
            update_date = current_timestamp(3)
        </set>
        WHERE user_id = #{id}
        </script>""")
    fun updateUser(user: BwUser): Int

    /**
     * User#id, emailValid, emailToken, userToken may be set.
     *
     * @param user
     * -
     */
    @Update("""<script>
        UPDATE bw_user
        <set>
            <when test='emailValid != null'>
                email_valid = #{emailValid},
            </when>
            <when test='emailToken != null'>
                email_token = #{emailToken},
            </when>
            <when test='userToken != null'>
                user_token = #{userToken},
            </when>
        </set>
        WHERE user_id = #{id}
        </script>""")
    fun updateUserToken(user: BwUser): Int

    /**
     * for user-login use-case.
     */
    @Insert("""
        INSERT INTO bw_user_login (session_id, user_id, login_time, ip_addr, dev_id, share_salt)
        VALUES (#{id}, #{userId}, #{loginTime}, #{ipAddr}, #{devId}, #{shareSalt})
        """)
    fun insertUserLogin(userLogin: BwUserLogin): Int

    @Delete("""
        DELETE FROM bw_user_login
        WHERE session_id = #{sid}
        """)
    fun deleteUserLogin(sid: String): Int

    @Delete("""
        DELETE FROM bw_user_login
        WHERE login_time > date_sub(curdate(), interval 1 month)
        """)
    fun deleteUserLoginObsolete(): Int

    @Select("""<script>
        SELECT ul.session_id AS id,
        ul.user_id AS userId,
        ul.login_time AS loginTime,
        ul.ip_addr AS ipAddr,
        ul.dev_id AS devId,
        ul.share_salt AS shareSalt,
        u.user_name AS userName,
        u.firm_id AS firmId,
        f.firm_name AS firmName
        FROM bw_user_login ul JOIN bw_user u ON (ul.user_id = u.user_id)
        LEFT JOIN bw_firm f ON (u.firm_id = f.firm_id)
        WHERE ul.user_id = #{userId}
        AND ul.dev_id = #{devId}
        AND ul.session_id = #{sessionId}
        </script>""")
    fun selectUserLogin(lr: LoginRequest): BwUserLogin

    @Select("""<script>
        SELECT ul.session_id AS id,
        ul.user_id AS userId,
        ul.login_time AS loginTime,
        ul.ip_addr AS ipAddr,
        ul.dev_id AS devId,
        ul.share_salt AS shareSalt,
        u.user_name AS userName,
        u.firm_id AS firmId,
        f.firm_name AS firmName
        FROM bw_user_login ul JOIN bw_user u ON (ul.user_id = u.user_id)
        LEFT JOIN bw_firm f ON (u.firm_id = f.firm_id)
        WHERE f.firm_id LIKE #{firmId}
            <when test='userId != null'>
                AND ul.user_id LIKE #{userId}
            </when>
            <when test='startTime != null'>
                AND ul.login_time &gt;= #{startTime}
            </when>
            <when test='endTime != null'>
                AND ul.login_time &lt; #{endTime}
            </when>
            <when test='sid != null'>
                AND ul.session_id = #{sid}
            </when>
        </script>""")
    fun listUserLogin(@Param("firmId") firmId: String, @Param("userId") userId: String?,
                      @Param("startTime") startTime: Date?, @Param("endTime") endTime: Date?,
                      @Param("sessionId") sessionId: String?): List<BwUserLogin>

    @Select("""
        SELECT right_name AS `name`, right_desc AS rightDesc
        FROM bw_right
        """)
    fun rightList(): List<BwRight>

    @Select("SELECT role_name as `name`, role_desc as roleDesc FROM bw_role")
    fun roleList(): List<BwRole>

    @Select("""
        SELECT ur.user_id AS userId
        , ur.role_name as `name`
        , r.role_desc as roleDesc
        FROM bw_user_role ur JOIN bw_role r ON ur.role_name = r.role_name
        WHERE ur.user_id = #{userId}
        """)
    fun userRoleList(@Param("userId") userId: String): List<BwRole>

    @Select("""<script>
        SELECT DISTINCT r.right_name AS `name`
        , r.right_desc AS rightDesc
        FROM bw_user_role ur
        JOIN bw_role_right rr ON ur.role_name = rr.role_name
        JOIN bw_right r ON rr.right_name = r.right_name
        WHERE ur.user_id = #{userId}
        <when test='rightName != null'>
            AND r.right_name = #{rightName}
        </when>
        </script>""")
    fun userRightList(@Param("userId") userId: String, @Param("rightName") rightName: String?): List<BwRight>

    @Select("""
        SELECT role_name as `name`, role_desc as roleDesc
        FROM bw_role WHERE role_name = #{name}
        """)
    fun selectRole(name: String): BwRole

    @Select("""
        SELECT rr.right_name as `name`
        , r.right_desc AS rightDesc
        FROM bw_role_right rr JOIN bw_right r ON rr.right_name = r.right_name
        WHERE rr.role_name = #{roleName}
        """)
    fun roleRightList(roleName: String): List<BwRight>

    @Insert("""
		INSERT into bw_role(role_name, role_desc)
		VALUES(#{name}, #{roleDesc})
        """)
    fun insertRole(role: BwRole): Int

    /**
     * <pre>
     * <parameterMap type="map" id="roleRight">
     * <parameter property="roleName"></parameter>
     * <parameter property="rightName"></parameter>
    </parameterMap> *
    </pre> *
     *
     * @param map
     * - roleName, rightName
     */
    @Insert("""
	    INSERT INTO bw_role_right (role_name, right_name)
	    VALUES(#{roleName}, #{rightName})
        """)
    fun insertRoleRight(@Param("roleName") roleName: String, @Param("rightName") rightName: String?): Int

    /**
     * <pre>
     * <parameterMap type="map" id="userRole">
     * <parameter property="userId"></parameter>
     * <parameter property="roleName"></parameter>
    </parameterMap> *
    </pre> *
     *
     * @param map
     * - userId, roleName
     */
    @Insert("""
        INSERT INTO bw_user_role(user_id, role_name)
        VALUES(#{userId}, #{roleName})
        """)
    fun insertUserRole(@Param("userId") userId: String, @Param("roleName") roleName: String): Int

    @Update("""
		UPDATE bw_role set
		role_name = #{name},
		role_desc = #{roleDesc}
		WHERE role_name = #{name}
        """)
    fun updateRole(role: BwRole): Int

    @Delete("""
        DELETE FROM bw_role
        WHERE role_name = #{roleName}
        """)
    fun deleteRole(roleName: String): Int

    @Delete("""
        DELETE FROM bw_role_right
        WHERE role_name = #{roleName}
        """)
    fun deleteRoleRight(roleName: String): Int

    @Delete("""
        DELETE FROM bw_user_role
        WHERE user_id = #{userId}
        """)
    fun deleteUserRole(userId: String): Int
}